<?php

class MemberMapper extends DataMapper
{
    const MODEL_CLASS = 'Member';

    // ------------- 更新系クエリ -----------------
    
    function login($data)
    {
        //SELECT
        $pdo = $this->getPDO();
        
        $sql = '
            SELECT
                   member.member_id, login_mail, login_pass, level
              FROM member
             WHERE member.login_mail = ?
               AND member.login_pass = ?
        ';
        $login_mail = $data->login_mail;
        $login_pass = Access::makeHash($data->login_pass);
        
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(1, $login_mail, PDO::PARAM_STR);
        $stmt->bindParam(2, $login_pass, PDO::PARAM_STR);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        $result = $stmt->fetch();
        if (!$result) return false;

        User::loginAccount($result);
        
        //insert
        $stmt = $pdo->prepare('
            INSERT INTO member_login_history(member_id, remote_ip, user_agent)
            VALUES (?, ?, ?)
        ');
        $stmt->bindParam(1, $member_id,  PDO::PARAM_INT);
        $stmt->bindParam(2, $remote_ip,  PDO::PARAM_STR);
        $stmt->bindParam(3, $user_agent, PDO::PARAM_STR);
        
        $member_id  = $result->member_id;
        $remote_ip  = $_SERVER['SERVER_ADDR'];
        $user_agent = $_SERVER['HTTP_USER_AGENT'];
        $stmt->execute();
        
        //returning
        return true;
    }

    function editProfile(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();

        $modelClass = static::MODEL_CLASS;
        $editor_id = $loader->solo;
        $member_id = $loader->solo;

        $stmtPost = $pdo->prepare('
            INSERT INTO post_history(post_id, editor_id)
            VALUES ((SELECT temp.post_id FROM post_history temp WHERE temp.history_id = 
                      (SELECT max(temp2.history_id) FROM member_history temp2 WHERE temp2.member_id = ? GROUP BY temp2.member_id))
                    , ?)
        ');
        $stmtPost->bindParam(1, $editor_id, PDO::PARAM_INT);
        $stmtPost->bindParam(2, $editor_id, PDO::PARAM_INT);

        $stmtMemberHistory = $pdo->prepare('
            INSERT INTO member_history(history_id, member_id, name, note, birth_day, avatar)
            VALUES (?, ?, ?, ?, ?, ?)
        ');
        $stmtMemberHistory->bindParam(1, $history_id,  PDO::PARAM_INT);
        $stmtMemberHistory->bindParam(2, $member_id,   PDO::PARAM_INT);
        $stmtMemberHistory->bindParam(3, $name,        PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(4, $note,        PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(5, $birth_day,   PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(6, $avatar,      PDO::PARAM_STR);

        if (! is_array($data)) $data = array($data);
        
        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            
            $stmtPost->execute();
            $history_id = $pdo->lastInsertId('post_history_history_id_seq');
            
            $name       = $row->name;
            $note       = $row->note;
            $birth_day  = $row->birth_day->format('Y-m-d');
            $avatar     = $row->avatar;
            
            $stmtMemberHistory->execute();
        }
    }


    function insert(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        $modelClass = static::MODEL_CLASS;
        $editor_id = $loader->mapperParameters['editor_id'];
        
        $stmt = $pdo->prepare('
            INSERT INTO member(login_mail, login_pass, is_active, level)
            VALUES (?, ?, ?, ?)
        ');
        $stmt->bindParam(1, $login_mail,  PDO::PARAM_STR);
        $stmt->bindParam(2, $login_pass,  PDO::PARAM_STR);
        $stmt->bindParam(3, $is_active,   PDO::PARAM_BOOL);
        $stmt->bindParam(4, $level,       PDO::PARAM_INT);

        $stmtPost = $pdo->prepare('
            INSERT INTO post(editor_note)
            VALUES (?)
        ');
        $stmtPost->bindParam(1, $editor_note, PDO::PARAM_STR);

        $stmtPostHistory = $pdo->prepare('
            INSERT INTO post_history(editor_id, post_id)
            VALUES (?, ?)
        ');
        $stmtPostHistory->bindParam(1, $editor_id, PDO::PARAM_INT);
        $stmtPostHistory->bindParam(2, $post_id,   PDO::PARAM_STR);
        
        $stmtMemberHistory = $pdo->prepare('
            INSERT INTO member_history(history_id, member_id, name, note, birth_day, avatar)
            VALUES (?, ?, ?, ?, ?, ?)
        ');
        $stmtMemberHistory->bindParam(1, $history_id,  PDO::PARAM_INT);
        $stmtMemberHistory->bindParam(2, $member_id,   PDO::PARAM_INT);
        $stmtMemberHistory->bindParam(3, $name,        PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(4, $note,        PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(5, $birth_day,   PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(6, $avatar,      PDO::PARAM_STR);

        if (! is_array($data)) $data = array($data);
        
        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $login_mail = $row->login_mail;
            $login_pass = Access::makeHash($row->login_pass);
            $is_active  = $row->is_active;
            $level      = $row->level;
            $editor_note= $row->editor_note;
            
            $stmt->execute();
            $member_id = $pdo->lastInsertId('member_member_id_seq');
            
            $stmtPost->execute();
            $post_id = $pdo->lastInsertId('post_post_id_seq');
            
            $stmtPostHistory->execute();
            $history_id = $pdo->lastInsertId('post_history_history_id_seq');
            
            $name       = $row->name;
            $note       = $row->note;
            $birth_day  = $row->birth_day->format('Y-m-d');
            $avatar     = $row->avatar;
            $stmtMemberHistory->execute();
        }
    }




    function update(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();

        $modelClass = static::MODEL_CLASS;
        $editor_id = $loader->mapperParameters['editor_id'];
        $member_id = $loader->solo;

        $stmt = $pdo->prepare('
            UPDATE member
               SET login_mail  = ?,
                   login_pass  = ?,
                   is_active   = ?,
                   level       = ?,
                   update_date = now()
             WHERE member_id = ?
        ');
        $stmt->bindParam(1, $login_mail,PDO::PARAM_STR);
        $stmt->bindParam(2, $login_pass,PDO::PARAM_STR);
        $stmt->bindParam(3, $is_active, PDO::PARAM_BOOL);
        $stmt->bindParam(4, $level,     PDO::PARAM_INT);
        $stmt->bindParam(5, $member_id, PDO::PARAM_INT);

        $stmtEditor = $pdo->prepare('
            UPDATE post
               SET editor_note = ?
             WHERE post_id = ?
        ');
        $stmtEditor->bindParam(1, $editor_note, PDO::PARAM_STR);
        $stmtEditor->bindParam(2, $post_id,     PDO::PARAM_INT);

        $stmtPost = $pdo->prepare('
            INSERT INTO post_history(post_id, editor_id)
            VALUES (?, ?)
        ');
        $stmtPost->bindParam(1, $post_id,   PDO::PARAM_INT);
        $stmtPost->bindParam(2, $editor_id, PDO::PARAM_INT);

        $stmtMemberHistory = $pdo->prepare('
            INSERT INTO member_history(history_id, member_id, name, note, birth_day, avatar)
            VALUES (?, ?, ?, ?, ?, ?)
        ');
        $stmtMemberHistory->bindParam(1, $history_id,  PDO::PARAM_INT);
        $stmtMemberHistory->bindParam(2, $member_id,   PDO::PARAM_INT);
        $stmtMemberHistory->bindParam(3, $name,        PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(4, $note,        PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(5, $birth_day,   PDO::PARAM_STR);
        $stmtMemberHistory->bindParam(6, $avatar,      PDO::PARAM_STR);

        if (! is_array($data)) $data = array($data);
        errorUTF8();
        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $login_mail = $row->login_mail;
            $login_pass = Access::makeHash($row->login_pass);
            $is_active  = $row->is_active;
            $level      = $row->level;
            $editor_note= $row->editor_note;
            $post_id    = $row->post_id;
            
            $name       = $row->name;
            $note       = $row->note;
            $birth_day  = $row->birth_day->format('Y-m-d');
            $avatar     = $row->avatar;
            
            $stmt->execute();
            $stmtEditor->execute();
            
            $stmtPost->execute();
            $history_id = $pdo->lastInsertId('post_history_history_id_seq');
            
            $stmtMemberHistory->execute();
        }
    }

    function delete(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            DELETE FROM member
             WHERE member_id = ?
        ');
        $stmt->bindParam(1, $member_id, PDO::PARAM_INT);

        if (! is_array($data)) {
            $data = array($data);
        }
        foreach ($data as $row) {
            if (! $row instanceof $modelClass) {
                throw new InvalidArgumentException;
            }
            $member_id = $row->member_id;
            $result = $stmt->execute();
        }
    }

    //------------- 参照系クエリ ----------------

    function findByMail(modelLoader $loader)
    {
        $pdo = $this->getPDO();
        $sql = '
            SELECT login_mail
              FROM member
             WHERE member.login_mail = ?
             LIMIT 1
        ';
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_STR);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        $result = $stmt->fetch();
        
        return $stmt->fetch();
    }

    function findById(modelLoader $loader)
    {
        $pdo = $this->getPDO();
        $sql = '
            SELECT
                   post_id, member.member_id,
                   name, note, (SELECT editor_note FROM post WHERE post.post_id = history.post_id) editor_note,
                   level, login_mail, is_active,
                   birth_day, avatar
              FROM (SELECT history_id FROM post_history GROUP BY history_id) temp
              JOIN post_history history ON history.history_id = temp.history_id
              JOIN member_history member_history ON history.history_id = member_history.history_id
              LEFT JOIN member ON member.member_id = member_history.member_id
             WHERE member.member_id = ?
             ORDER BY history.publish_date DESC
             LIMIT 1
        ';
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        
        return $stmt->fetch();
    }

    function find(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        
        $order = $this->getOrderString($loader->order);
        $where = $this->getSearchString($loader->alike, 'ilike', "member.login_id||'\n'||coalesce(profile.name,'')", $pdo);
        $limit = $this->getLimitString($loader->limit, $loader->offset);

        if (!$countMode) {
            $query = '
            SELECT
                   post_id, member.member_id, temp.history_id, post_history.publish_date as update_date,
                   name, note, (SELECT editor_note FROM post WHERE post.post_id = post_history.post_id) editor_note,
                   level, login_mail, is_active, member.publish_date,
                   birth_day, avatar,
                   (SELECT history.publish_date
                      FROM member_login_history history
                     WHERE history.member_id = member.member_id
                  ORDER BY publish_date DESC
                     LIMIT 1) as login_date
              FROM member
              JOIN (SELECT max(a.history_id) history_id, a.member_id FROM member_history a GROUP BY a.member_id) temp ON temp.member_id = member.member_id
              JOIN member_history ON member_history.history_id = temp.history_id
              JOIN post_history ON post_history.history_id = temp.history_id
                  '.$where.'
                  '.$order.'
                  '.$limit.'
            ';
            $stmt = $pdo->query($query);
            $stmt->execute();
            return $this->_decorate($stmt, $query);
            
        } else {
            $query = '
                SELECT count(member.member_id) as _count,
                       '.$loader->limit.' as _limit
              FROM member
              JOIN (SELECT max(a.history_id) history_id, a.member_id FROM member_history a GROUP BY a.member_id) temp ON temp.member_id = member.member_id
              JOIN member_history ON member_history.history_id = temp.history_id
              JOIN post_history ON post_history.history_id = temp.history_id
                  '.$where.'
            ';
            
            $stmt = $pdo->query($query);
            $stmt->execute();
            $this->_decorate($stmt, $query);
            
            return $stmt->fetch();
        }
    }




    function findPanelById(modelLoader $loader)
    {
        $pdo = $this->getPDO();
        
        $sql = '
            SELECT
                   member_id, name, note, avatar, birth_day, (SELECT level FROM member temp WHERE temp.member_id = member.member_id) as level,
                   history.publish_date as update_date
              FROM member_history member
              JOIN post_history history ON history.history_id = member.history_id
             WHERE member_id = ?
             ORDER BY history.publish_date DESC
             LIMIT 1
        ';
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        return $stmt->fetch();
    }

    function findHistoryById(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        
        $limit = $this->getLimitString($loader->limit, $loader->offset);

        $id = $loader->mapperParameters['member_id'];
        
        if (!$countMode) {
            $query = '
            SELECT
                   remote_ip, user_agent, publish_date
              FROM member_login_history
             WHERE member_id = ?
             ORDER BY publish_date DESC
                  '.$limit.'
            ';
            $stmt = $pdo->prepare($query);
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->execute();
            return $this->_decorate($stmt, $query);
            
        } else {
            $query = '
                SELECT count(member_id) as _count,
                       '.$loader->limit.' as _limit
                       ,(SELECT name FROM member_history history WHERE history.member_id = login.member_id ORDER BY history_id DESC LIMIT 1) as _name
                  FROM member_login_history as login
                 WHERE member_id = ?
                 GROUP BY member_id
            ';
            $stmt = $pdo->prepare($query);
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->execute();
            $this->_decorate($stmt, $query);
            
            return $stmt->fetch();
        }
    }

}